#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/12/6 17:53
# @Author : 闫峰
# @File    : sku_filter.py
import datetime as datetime_1
import re
from datetime import datetime, timedelta
import json
from collections import defaultdict
import requests

from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
import os
import threading
import time
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.mongodb_utils import MongoDBLink
from utils.times_tool.time_control import count_time

current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])


def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')


class SkuFilter:
    db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "zhcxkj_center_category_attribute_prod")
    product = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-Product")
    db1 = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-strategy")
    db2 = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-JavaCommon")
    db3 = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-Warehouse")
    # 定义mongodb

    def __init__(self,strategy_id):
        self.strategy_id = strategy_id
        # 查询策略
        get_stragety = """

        SELECT
            a.`status`,
            a.product_status,
            a.min_batch,
            a.max_batch,
            a.min_one_sides,
            a.max_one_sides,
            a.min_three_sides,
            a.max_three_sides,
            a.min_package_weight,
            a.max_package_weight,
            a.coefficient,
            a.min_volume_weight,
            a.max_volume_weight,
            a.min_rate,
            a.max_rate,
            a.purchase_price_min,
            a.purchase_price_max,
            a.review_start_time,
            a.review_end_time,
            a.review_day_min,
            a.review_day_max,
            a.prohibit_platform_type,
            a.is_brand,
            a.infringe_type,
            a.product_type,
            GROUP_CONCAT( b.product_attribute_id ),
            a.delivery_warehouse,
            a.law_firm_type,
            a.is_group,
            a.prohibit_platform_site,
            a.holiday_site
        FROM
            `zhcxkj_center_strategy_comprehensive`.`t_shopee_product_rules_template` a
            LEFT JOIN `zhcxkj_center_strategy_comprehensive`.t_shopee_product_attribute_filtering b ON a.id = b.product_rules_template_id 
        WHERE
            a.id = %s 
        GROUP BY
            a.id
        """

        stragety = self.db1.fetch_all_tuple(get_stragety,(strategy_id,))
        if not len(stragety):
            raise
        if stragety[0][0]:
            raise
        self.status = stragety[0][0]
        self.product_status= list(map(int,stragety[0][1].split(",")))
        self.min_batch= stragety[0][2]
        self.max_batch= stragety[0][3]
        self.min_one_sides= stragety[0][4]
        self.max_one_sides= stragety[0][5]
        self.min_three_sides= stragety[0][6]
        self.max_three_sides= stragety[0][7]
        self.min_package_weight= stragety[0][8]
        self.max_package_weight= stragety[0][9]
        self.coefficient= stragety[0][10]
        self.min_volume_weight= stragety[0][11]
        self.max_volume_weight= stragety[0][12]
        self.min_rate= stragety[0][13]
        self.max_rate= stragety[0][14]
        self.purchase_price_min= stragety[0][15]
        self.purchase_price_max= stragety[0][16]
        self.review_start_time= stragety[0][17]
        self.review_end_time= stragety[0][18]
        self.review_day_min= stragety[0][19]
        self.review_day_max= stragety[0][20]
        self.prohibit_platform_type= stragety[0][21]
        self.prohibit_platform_site = stragety[0][29]
        if self.prohibit_platform_type:
            platform_type_set = set(self.prohibit_platform_type.split(","))
            get_no_sale_sku_list = """
            SELECT sku FROM `zhcxkj-center-product`.`rb_product_no_sale` WHERE platform_type in %s
                        """
            sku_list = self.product.fetch_all_tuple(get_no_sale_sku_list, (platform_type_set,))
            self.no_sale_sku_list = list(map(lambda x: x[0], sku_list))
        if self.prohibit_platform_site:
            platform_site_set = set(self.prohibit_platform_site.split(","))
            get_no_sale_site_list = """
            SELECT platform_type,site_code FROM `zhcxkj-center-data`.`t_platform_site` WHERE id IN %s
                        """
            site_res = self.db2.fetch_all(get_no_sale_site_list, (platform_site_set,))
            self.no_sale_site_sku_total_list = []
            for site in site_res:
                get_no_sale_site_sku_list = """
                SELECT sku FROM `zhcxkj-center-product`.`rb_product_no_sale`  
                WHERE platform_type = %s AND (country_shorthand='' OR country_shorthand = %s)
                            """
                sku_list = self.product.fetch_all_tuple(get_no_sale_site_sku_list, (site.get('platform_type'), site.get('site_code'),))
                self.no_sale_site_sku_list = list(map(lambda x: x[0], sku_list))
                self.no_sale_site_sku_total_list = self.no_sale_site_sku_total_list + self.no_sale_site_sku_list
        self.holiday_site = stragety[0][30]
        self.is_brand= stragety[0][22]
        self.infringe_type= stragety[0][23]
        self.product_type= stragety[0][24]
        self.product_attribute_id_list = list(map(int,stragety[0][25].split(",")))
        if stragety[0][26]:
            self.delivery_warehouse_list = list(map(int, stragety[0][26].split(",")))
        else:
            self.delivery_warehouse_list = None
        self.law_firm_type = stragety[0][27]
        if self.law_firm_type == 0:
            self.law_firm_type = [-1, 0]
        elif self.law_firm_type == 1:
            self.law_firm_type = [1]
        else:
            self.law_firm_type = [-1, 0, 1]
        self.is_group = stragety[0][28]
        if self.is_group == 0:
            self.is_group = [0]
        elif self.is_group == 1:
            self.is_group = [1]
        else:
            self.is_group = [0,1]

    @count_time
    def sku_filter(self):
        # 分批次拉取数据源 在线的listing数据

        sql1 = f"""
        SELECT
            rp.id, 
            rp.customer_id,
            rp.sku,
            rp.product_group_sku, -- 是否变体 3
            rp.last_buy_price,  -- 采购价 4 
            rp.is_group,  -- 是否组合产品 5
            rp.product_state, -- 产品状态 6
            rp.product_property,  -- 侵权风险  7
            rp.brand_id,  -- 是否品牌 8 
            rp.with_battery, -- 物流属性 9 
            rp.min_sale_num , -- 起批量 10
        CASE WHEN (rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0) THEN rp.pack_length ELSE rp.length END AS length, -- 长 11
        CASE WHEN (rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0) THEN rp.pack_width ELSE rp.width  END AS width,-- 款 12
        CASE WHEN (rp.pack_length > 0 AND rp.pack_width > 0 AND rp.pack_height > 0 )THEN rp.pack_height ELSE rp.height END AS height ,-- 高 13
        CASE WHEN rp.pack_weight=0 THEN rp.gross_weight ELSE rp.pack_weight END	AS pack_weight, -- 重量 14
        rpa.check_time,  -- 终审时间 15
        rp.is_group, -- 产品类型 16
        rp.default_local_warehouse, -- 默认发货仓库 17
        rp.has_law_firm_agent -- 律所标识 18
        FROM `zhcxkj-center-product`.`rb_product` rp
        JOIN `zhcxkj-center-product`.rb_product_admin rpa ON rpa.customer_id = rp.customer_id AND rpa.sku = rp.sku AND rpa.check_status = 1
        AND rp.delete_status=0
        WHERE rp.id>%s
        AND rp.customer_id = 1
        order by id 
        LIMIT 1000
        """
        id_index = 0
        sku_set_total = set()
        while True:
            print(f"index={id_index}")
            data_list = self.product.fetch_all_tuple(sql1, (id_index,))
            if not len(data_list):
                break
            id_index = data_list[-1][0]
            data_set = set(data_list)

            if not data_set:
                continue
            # 参与过滤的sku必须是终审通过的sku
            data_set = set(filter(lambda x: x[16]==0, data_set))
            if not data_set:
                continue
            # 产品状态 product_state  0.正常；1.仅批量；2.停产；3.暂时缺货；4.锁定；5.清库
            data_set = set(filter(lambda x:x[6] in self.product_status,data_set))
            if not data_set:
                continue
            # 物流属性
            if self.product_attribute_id_list:
                data_set = set(filter(lambda x: x[9] in self.product_attribute_id_list, data_set))
            if not data_set:
                continue
            if not data_set:
                continue
            # 律所标识(非必填)
            if self.law_firm_type :
                data_set = set(filter(lambda x: x[18] in self.law_firm_type, data_set))
            if not data_set:
                continue
            # 起批量  min_sale_num
            if self.min_batch or self.max_batch:
                data_set = set(filter(lambda x: self.min_batch <= x[10] <= self.max_batch, data_set))
            if not data_set:
                continue
            # 单边 每条边都是符合
            if self.min_one_sides or self.max_one_sides:
                data_set = set(filter(lambda x: self.min_one_sides <= x[11] <= self.max_one_sides and self.min_one_sides <= x[12] <= self.max_one_sides and self.min_one_sides <= x[13] <= self.max_one_sides, data_set))
            if not data_set:
                continue
            # 三边之和
            if self.min_three_sides or self.max_three_sides:
                data_set = set(filter(lambda x: self.min_three_sides <= (x[11]+ x[12] + x[13]) <= self.max_three_sides, data_set))
            if not data_set:
                continue
            # 包裹重量
            if self.min_package_weight or self.max_package_weight:
                data_set = set(filter(lambda x: self.min_package_weight <= (x[14]/1000)  <= self.max_package_weight, data_set))
            if not data_set:
                continue
            # 体积重
            if self.min_volume_weight or self.max_volume_weight:
                data_set = set(filter(lambda x: self.min_volume_weight <= ((x[11] * x[12] * x[13]) / self.coefficient) <= self.max_volume_weight, data_set))
            if not data_set:
                continue
            # 抛率
            if self.min_rate or self.max_rate:
                try:
                    data_set = set(filter(lambda x: x[14], data_set))
                    data_set = set(filter(lambda x: self.min_rate <= ((x[11] * x[12] * x[13]) / self.coefficient / x[14]*1000) <= self.max_rate, data_set))
                except:
                    print()
                    raise
            if not data_set:
                continue
            # 采购价
            if self.purchase_price_min or self.purchase_price_max:
                data_set = set(filter(lambda x: self.purchase_price_min <= x[4] <= self.purchase_price_max, data_set))
            if not data_set:
                continue
            # 侵权类型
            # 1非仿牌 2仿牌 3禁售
            if self.infringe_type:
                data_set = set(filter(lambda x: x[7] ==int(self.infringe_type), data_set))
            if not data_set:
                continue
            # 产品类型
            # 0 全部 1 单体 2多变体
            if self.product_type:
                if self.product_type ==1:
                    data_set = set(filter(lambda x: not bool(x[3]), data_set))
                elif self.product_type == 2:
                    data_set = set(filter(lambda x: bool(x[3]), data_set))
            if not data_set:
                continue
            # 是否组合产品
            # 0 非组合 1 组合 2全部
            if self.is_group :
                data_set = set(filter(lambda x: x[5] in self.is_group, data_set))
            if not data_set:
                continue
            # 是否品牌产品
            # 0 全部 1 是 2 否
            if self.is_brand:
                if self.is_brand ==1:
                    data_set = set(filter(lambda x: bool(x[8]), data_set))
                elif self.is_brand ==2:
                    data_set = set(filter(lambda x: not bool(x[8]), data_set))
            if not data_set:
                continue
            # 终审时间 天数范围
            if self.review_day_min or self.review_day_max:
                pass

            if not data_set:
                continue
            # 终审时间  日期范围
            if self.review_start_time:
                data_set = set(filter(lambda x: self.review_start_time <= x[15] <= self.review_end_time, data_set))
            if not data_set:
                continue
# TOD0 这里注意业务类型的取值、禁售、风险等级的配置(in/not in;平台与站点)：
#             # 禁售平台
#             if self.prohibit_platform_type:
#                 data_set = set(filter(lambda x: x[2] not in self.no_sale_sku_list, data_set))
#             if not data_set:
#                 continue
            # 禁售平台站点
            if self.prohibit_platform_site:
                data_set = set(filter(lambda x: x[2] not in self.no_sale_site_sku_total_list, data_set))
            if not data_set:
                continue
            # 仅刊登业务需进行节日站点过滤
            if self.holiday_site:
                data_set = set(filter(lambda x: x[2] not in self.get_holiday_sku(), data_set))
            if not data_set:
                continue
            # 发货仓库(非必填)
            if self.delivery_warehouse_list:
                data_set = self.get_good_num(data_set)
                sku_set_total |= data_set
            else:
                sku_set = set(map(lambda x: x[2], data_set))
                sku_set_total |= sku_set

            # sku_set = set(map(lambda x: x[2], data_set))
            # sku_set_total |= sku_set

        INFO.logger.info(f"最终筛选{len(sku_set_total)}")
        # INFO.logger.info(f"sku_set_total={sku_set_total}")
        get_actual_sku_list = """
                    SELECT sku FROM `zhcxkj_center_strategy_comprehensive`.`t_shopee_product_rules_sku` WHERE `product_rules_template_id` = %s
                    """
        actual_sku_list = self.db1.fetch_all_tuple(get_actual_sku_list, (self.strategy_id,))
        actual_sku_set = set(map(lambda x: x[0], actual_sku_list))
        if sku_set_total == actual_sku_set:
            pass
        else:
            ERROR.logger.error(f"错误，差异如下")
            # ERROR.logger.error(f"{sku_set_total-actual_sku_set}")
            print(f"{sku_set_total - actual_sku_set}")
            print(f"{actual_sku_set - sku_set_total}")

    def get_good_num(self, data_set):
        data_list = list(map(lambda x: x[2], data_set))
        # data_list=[]
        # for elments in data_set:
        #     data_list.append(elments[2])
        good_num_sku_list = []
        no_good_num_sku_list = []
        for sku in data_list:
            is_group_sql = """SELECT is_group FROM `zhcxkj-center-product`.`rb_product` WHERE sku=%s;"""
            is_group_sql_res = self.product.fetch_all(is_group_sql, (sku,))
            if is_group_sql_res:
                if is_group_sql_res[0]['is_group'] == 0:
                    filter_local_good_num = """
                    SELECT SUM(good_num) good_num FROM zhcxkj_center_warehouse.dynamic_inventory WHERE sku=%s AND warehouse_id IN %s;
                    """
                    res1 = self.db3.fetch_all(filter_local_good_num, (sku, self.delivery_warehouse_list,))
                    filter_overseas_good_num = """
                    SELECT hp.sku,hp.wareHouse_id FROM `zhcxkj_center_warehouse`.`rb_ware_house` h 
                    LEFT JOIN zhcxkj_center_warehouse.rb_ware_house_product hp ON h.id=hp.wareHouse_id 
                    WHERE h.ware_house_type>1 AND hp.sku=%s and hp.wareHouse_id IN %s;
                    """
                    res2 = self.db3.fetch_all(filter_overseas_good_num, (sku, self.delivery_warehouse_list,))
                    if res1 or res2:
                        good_num_sku_list.append(sku)
                    elif not res1 and not res2:
                        no_good_num_sku_list.append(sku)
                else:
                    get_child_sku = "SELECT sku,sku_num FROM zhcxkj_center_product.rb_product_group_sku " \
                                    "WHERE group_sku=%s;"
                    get_child_sku_res = self.product.fetch_all(get_child_sku, (sku,))

                    if get_child_sku_res:
                        group_sku_list = []
                        for child in get_child_sku_res:
                            group_sku_list.append(child.get('sku'))
                        group_sku_set = tuple(group_sku_list)
                        filter_local_good_num = """
                                SELECT sku,warehouse_id FROM zhcxkj_center_warehouse.dynamic_inventory 
                                WHERE sku IN %s AND warehouse_id IN %s group by sku,warehouse_id;
                                """
                        filter_local_good_num_res = self.db3.fetch_all(filter_local_good_num,
                                                                      (group_sku_set, self.delivery_warehouse_list,))
                        filter_overseas_good_num = """
                                SELECT hp.sku,hp.wareHouse_id FROM `zhcxkj_center_warehouse`.`rb_ware_house` h 
                                LEFT JOIN zhcxkj_center_warehouse.rb_ware_house_product hp ON h.id=hp.wareHouse_id 
                                WHERE h.ware_house_type>1 AND hp.sku IN %s and hp.wareHouse_id IN %s group by sku,warehouse_id;
                                """
                        filter_overseas_good_num_res = self.db3.fetch_all(filter_overseas_good_num,
                                                                         (group_sku_set,
                                                                          self.delivery_warehouse_list,))
                        # 判断子sku是否存在于同一个仓库
                        if not filter_local_good_num_res and not filter_overseas_good_num_res:
                            warehouse_total = []
                            print(f"未查到子SKU库存: {sku}")
                        elif filter_local_good_num_res and not filter_overseas_good_num_res:
                            warehouse_total = filter_local_good_num_res
                        elif not filter_local_good_num_res and filter_overseas_good_num_res:
                            warehouse_total = filter_overseas_good_num_res
                        else:
                            warehouse_total = filter_local_good_num_res + filter_overseas_good_num_res

                        sku_warehouse_dict = defaultdict(list)
                        if warehouse_total:
                            for item in warehouse_total:
                                sku_child = item['sku']
                                warehouse_id = item.get('warehouse_id') or item.get('wareHouse_id')
                                if sku_child not in sku_warehouse_dict:
                                    sku_warehouse_dict[sku_child] = []
                                sku_warehouse_dict[sku_child].append(warehouse_id)
                            warehouse_set_list = [set(warehouse_list) for warehouse_list in sku_warehouse_dict.values()]

                            # 将集合转换为仓库列表
                            warehouse_list = [list(s) for s in warehouse_set_list]
                            # 获取子SKU多个列表的交集
                            common_warehouse = set(warehouse_list[0]).intersection(*warehouse_list[1:])
                            if len(common_warehouse) >= 1:
                                good_num_sku_list.append(sku)
                                print("子SKU都在同一仓库中")
                            else:
                                no_good_num_sku_list.append(sku)
                                print("子SKU不在同一仓库中")
                        else:
                            no_good_num_sku_list.append(sku)
                    else:
                        no_good_num_sku_list.append(sku)
                        print(f"{sku}在rb_product_group_sku表没查询到子sku")
        # no_good_num_sku_set = set(no_good_num_sku_list)
        data_set = set(good_num_sku_list)
        return data_set


    def get_label_id_tuple(self):
        sql = """
                    SELECT
                        label_id,
                        time_type,#0-固定日期 1-指定日期区间2-指定周期
                        holiday_start_time,
                        holiday_end_time,
                        publish_start_day,
                        publish_end_day,
                        promotion_start_day,
                        promotion_end_day 
                    FROM
                        `zhcxkj-center-data`.`t_holiday_label_detail` 
                    WHERE
                        `status` = 1 
                        AND is_deleted = 0 
                        AND (label_site='' OR label_site IN (%s))
                """
        label_id_tuple = self.db2.fetch_all_tuple(sql, (self.holiday_site,))
        label_id_list = []
        # print(datetime.now().strftime('%Y-%m-%d'))
        # 计算符合当前日期符合刊登的节假日label_id
        for data in label_id_tuple:
            if data[1] == 0:
                # 日期格式转换（如：4月1日 转换为 2024-04-01）
                # 计算当前时间是否符合可刊登时间
                publish_start_day = datetime.strptime(f'{datetime.now().year}年{data[2]}', '%Y年%m月%d日') - timedelta(
                    days=int(data[4]))
                publish_end_day = datetime.strptime(f'{datetime.now().year}年{data[2]}', '%Y年%m月%d日') - timedelta(
                    days=int(data[5]))
                if publish_start_day <= datetime.now() <= publish_end_day:
                    label_id_list.append(data)
            elif data[1] == 1:
                month_min = int(re.search(r"(.*)月", data[2]).group(1))
                month_max = int(re.search(r"(.*)月", data[3]).group(1))
                # 考虑跨年场景
                if month_min <= month_max:
                    publish_start_day1 = datetime.strptime(f'{datetime.now().year}年{data[2]}', '%Y年%m月%d日') - timedelta(
                        days=int(data[4]))
                    publish_end_day1 = datetime.strptime(f'{datetime.now().year}年{data[3]}', '%Y年%m月%d日') - timedelta(
                        days=int(data[5]))
                    if publish_start_day1 <= datetime.now() <= publish_end_day1:
                        label_id_list.append(data)
                elif month_min > month_max:
                    publish_start_day1 = datetime.strptime(f'{datetime.now().year}年{data[2]}',
                                                           '%Y年%m月%d日') - timedelta(days=int(data[4]))
                    publish_end_day1 = datetime.strptime(f'{datetime.now().year+1}年{data[3]}', '%Y年%m月%d日') - timedelta(
                        days=int(data[5]))
                    if publish_start_day1 <= datetime.now() <= publish_end_day1:
                        label_id_list.append(data)
            elif data[1] == 2:
                # 日期格式转换（如：六月第三周星期日 转换为 2024-06-16）
                # 检查字符串是否符合正则表达式
                str_date = re.search(r"(.*)月第(.)周星期(.)", data[2])
                if str_date:
                    # 提取数字
                    month, week, day = str_date.groups()
                    # 将大写转换为数字
                    months = {"一": 1, "二": 2, "三": 3, "四": 4, "五": 5, "六": 6, "七": 7, "八": 8, "九": 9, "十": 10, "十一": 11,
                              "十二": 12}
                    weeks = {"一": 1, "二": 2, "三": 3, "四": 4, "五": 5}
                    days = {"一": 0, "二": 1, "三": 2, "四": 3, "五": 4, "六": 5, "日": 6}
                    month = months.get(month)
                    week = weeks.get(week)
                    day = days.get(day)
                    year = datetime.now().year
                    # print(month, week, day)
                    first_day = datetime_1.date(year, month, 1)
                    first_weekday = first_day.weekday()
                    days_to_add = (day - first_weekday + 7) % 7
                    first_monday = first_day + timedelta(days=days_to_add)
                    second_monday = first_monday + timedelta(weeks=week - 1)
                    # 计算当前时间是否符合可刊登时间
                    publish_start_day = second_monday - timedelta(days=int(data[4]))
                    publish_end_day = second_monday - timedelta(days=int(data[5]))
                    if publish_start_day <= datetime.now().date() <= publish_end_day:
                        label_id_list.append(data)
                else:
                    print("字符串不符合要求")
        # 得到最终符合的活动ID集合
        label_id_tuple = tuple(map(lambda x: x[0], label_id_list))
        return label_id_tuple



    def get_holiday_sku(self):
        # 获取节日sku
        label_id_tuple = self.get_label_id_tuple()
        sql = """
            SELECT
                sku 
            FROM
                `zhcxkj-center-product`.`rb_product_label_comprehensive` 
            WHERE
                label_id IN %s 
            GROUP BY
                sku
                """
        holiday_sku = self.product.fetch_all_tuple(sql, (label_id_tuple,))
        return holiday_sku




if __name__ == '__main__':
    t = SkuFilter(6)
    t.sku_filter()
    # sku_set1 = {'12216213', '12019588', '12137109'}
    # t.get_good_num(sku_set1)
